-----------------------------------------------------
-- @name: clustering factor
-- @author: dion cho
-- @description: test for clustering factor
-----------------------------------------------------

-- create objects
drop table t_clsf purge;

create table t_clsf(c1 int, c2 int);

create index t_clsf_i1 on t_clsf(c1);
create index t_clsf_i2 on t_clsf(c2);

-- c1 : same order as table, c2 : random
insert into t_clsf
select rownum, lvl
from 
  (select level lvl 
  from dual connect by level <= 10000 
  order by dbms_random.random) 
;

commit;

-- gather stats
exec dbms_stats.gather_table_stats(user, 't_clsf', -
  method_opt=>'for all columns size 1', -
  cascade=>true);
  
-- check index stats
@tab_stat t_clsf

@ind_stat t_clsf


-----------------------------------------------------
-- see how clustering factor affects the access path

-- this is FTS(only for 1%)
explain plan for
select /*+ bad clsf */ * 
from t_clsf
where c2 between 1 and 100;

@plan

-- this is IRS
explain plan for
select /*+ good clsf */ * 
from t_clsf
where c1 between 1 and 100;

@plan

-- cost of IRS on bad CF
explain plan for
select /*+ bad clsf index(t_clsf) */ * 
from t_clsf
where c2 between 1 and 100;

@plan


----------------------------------------------------------------------
-- compare real workload
alter session set statistics_level = all;

select /*+ index(t_clsf) */ *
from t_clsf
where c1 between 1 and 100
union all
select /*+ full(t_clsf) */ *
from t_clsf
where c1 between 1 and 100
union all
select /*+ index(t_clsf) */ *
from t_clsf
where c2 between 1 and 100
;

@stat

alter session set statistics_level = typical;


------------------------------------------------------------------------
-- why big difference on logical reads(103:150)
-- check buffer pinning
@capture_on

@mysid

@mon_on &v_sid

select /*+ good_cf index(t_clsf) */ *
from t_clsf
where c1 between 1 and 100
;

@mon_off

select /*+ bad_cf index(t_clsf) */ *
from t_clsf
where c2 between 1 and 100
;

@mon_off2

@mon_show2

@capture_off
